"""
Case Type   : merge into
Case Name   : merge into同时并发update/delete
Create At   : 2024/07/25
Owner       : songjing20
Description :
    1.建表插入数据
    2.session1连接数据库执行update
    3.session2执行merge into
    4.查询表数据
    5.清理环境
Expect      :
    1.建表成功
    2.执行成功
    3.session1 commit后有数据
    4.表1两条数据,表2 1条数据
    5.成功
History     :
"""

import os
import time
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class MergeInto(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.tb1_name = 't_mergeinto_0037_1'
        self.tb2_name = 't_mergeinto_0037_2'

    def test_mergeinto(self):
        text = '-----step1:建表插入数据;expect:建表成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.tb1_name},{self.tb2_name} cascade;
            create table {self.tb1_name}(a int, b int, c int);
            create table {self.tb2_name}(a int);
            insert into {self.tb1_name} values(1, 1, 1);
            insert into {self.tb2_name} values(1);''')
        self.log.info(res)
        self.assertEqual(res.count(self.constant.CREATE_TABLE_SUCCESS), 2,
                         f"执行失败: {text}")
        self.assertEqual(res.count(self.constant.INSERT_SUCCESS_MSG), 2,
                         f"执行失败: {text}")

        text2 = '-----step2:session1连接数据库执行update;expect:执行成功-----'
        self.log.info(text2)
        sql1 = f'''begin;
        update {self.tb1_name} set a=22 where a=1;
        select pg_sleep(3);
        end;'''
        self.log.info(sql1)
        session1 = ComThread(self.pri_sh.execut_db_sql, args=(sql1,))
        session1.setDaemon(True)

        text3 = '-----step3:session2执行merge into;expect:成功-----'
        self.log.info(text3)
        sql2 = f'''begin;
        select pg_sleep(1);
        merge into {self.tb1_name} p1 using (select * from {self.tb2_name}) p2 
        on (p1.a=p2.a) when matched then update set p1.b=66 
        when not matched then insert (a,b,c) values(8,8,8);
        end;'''
        session2 = ComThread(self.pri_sh.execut_db_sql, args=(sql2,))
        session2.setDaemon(True)
        session1.start()
        session2.start()
        session1.join(10)
        session2.join(10)
        result1 = session1.get_result()
        self.log.info(f'session1_results: {result1}')
        result2 = session2.get_result()
        self.log.info(f'session2_results: {result2}')
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result1,
                      f"执行失败: {text2}")
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, result1,
                      f"执行失败: {text2}")
        self.assertIn('MERGE 1', result2, f"执行失败:{text3}")
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, result2,
                      f"执行失败: {text3}")

        text = '-----step4:查询表数据;expect:表1两条数据,表2 1条数据-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"select * from {self.tb1_name};select * from {self.tb2_name};")
        self.log.info(res)
        self.assertIn('22 | 1 | 1\n  8 | 8 | 8\n(2 rows', res,
                      f"执行失败: {text}")
        self.assertIn(' 1\n(1 row)', res, f"执行失败: {text}")

    def tearDown(self):
        text4 = '-----step5:清理环境 expect:成功-----'
        self.log.info(text4)
        drop_db_res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.tb1_name},{self.tb2_name} cascade;")
        self.log.info(drop_db_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_db_res,
                      f"执行失败: {text4}")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
